﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Security.Cryptography;

namespace LoginServer
{
    public class GetDB
    {
        string connectionString = "Server=localhost;Port=3306;Database=test;Uid=root;Pwd=\"\";";
        //string connectionString = "Server=yugioh.vn;Port=3306;Database=test;Uid=adminyugi;Pwd=admin123;";
        //string connectionString = "Server=yugioh.vn;Port=3306;Database=yugioh;Uid=adminyugi;Pwd=admin123;";
        //string connectionString = "Server=112.213.87.31;Port=3306;Database=yugioh;Uid=adminyugi;Pwd=admin123;";
        MySqlConnection connection;
        Dictionary<long, string> TempNickStore = new Dictionary<long, string>();
        public GetDB()
        {
            connection = new MySqlConnection(connectionString);
        }
        void Connect()
        {
            if (connection.State == System.Data.ConnectionState.Closed)
                connection.Open();
        }
        void Disconnect()
        {
            connection.Close();
        }
        bool Ping()
        {
            return connection.Ping();
        }
        public string GetNickName(int ID)
        {
            if (TempNickStore.ContainsKey(ID))
                return TempNickStore[ID];
            string commandString = "select NickName from user where ID=" + ID.ToString();
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            if (reader.FieldCount != 1)
                return "error";
            while (reader.Read())
            {
                string nick = reader.GetString(0);
                TempNickStore[ID] = nick;
                reader.Close();
                Disconnect();
                return nick;
            }
            reader.Close();
            Disconnect();
            return "";
        }

        public string GetCardsOfPlayer(int IDPlayer)
        {
            string commandString = "select IDCard from cardowner where IDPlayer=" + IDPlayer.ToString();
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            string result = "";
            bool isFirst = true;
            while (reader.Read())
            {
                if (isFirst)
                    result += reader.GetInt32(0).ToString();
                else
                    result += "," + reader.GetInt32(0).ToString();
            }
            reader.Close();
            Disconnect();
            return result;
        }

        public void UpdateCard(int player_id, int card_typeid, int numberindeck)
        {
            string commandString = String.Format("UPDATE `cardowner` SET `SoLuongTrongBoBai`='{0}' WHERE (`IDCard`='{1}') AND (`IDPlayer`='{2}')", numberindeck, card_typeid, player_id);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            int res = command.ExecuteNonQuery();
            if (res == 0)
                Console.WriteLine("Error When Excute : {0}", commandString);
            Disconnect();
        }

        #region Login-Logout
        /// <summary>
        /// Login User
        /// </summary>
        /// <param name="Email">Email to Auth</param>
        /// <param name="password">Password to Auth</param>
        /// <param name="auto_update">Auto updateTable When Login Success</param>
        /// <returns></returns>
        public PlayerInfo Login(string Email, string password, bool auto_update)
        {
            SHA1 hasher = SHA1.Create();
            byte[] res = hasher.ComputeHash(GetByte(password));
            string pass = GetString(res);

            string commandString = "select * from user where Email='" + Email + "' and Password='" + pass + "'";
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            PlayerInfo info = null;
            if (reader.HasRows)
            {
                reader.Read();
                info = new PlayerInfo();
                info.ID = reader.GetInt32("ID");
                info.Email = reader.GetString("Email");
                info.Password = reader.GetString("Password");
                info.NickName = reader.GetString("NickName");
                info.IsOnline = reader.GetInt32("IsOnline");
                reader.Close();
            }
            else
            {
                Disconnect();
                return null;
            }
            Disconnect();
            if (auto_update)
                UpdateOnline(info.ID, true);



            return info;
        }
        public void Logout(int ID)
        {
            UpdateOnline(ID, false);
        }
        private void UpdateOnline(int ID, bool value)
        {
            string commandString = String.Format("UPDATE user SET IsOnline='{0}' WHERE (ID={1})", value ? 1 : 0, ID);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            int res = command.ExecuteNonQuery();
            if (res == 0)
                Console.WriteLine("Error When Excute : {0}", commandString);
            Disconnect();
        }
        #endregion

        #region Table
        internal List<TableInfo> GetAllTableInRoom(int RoomID)
        {
            string commandString = string.Format("SELECT * FROM `table` where PlayerID1 in (SELECT ID FROM `user` where RoomID ='{0}')", RoomID);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            TableInfo info = null;
            List<TableInfo> res = new List<TableInfo>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int tableid = reader.GetInt32("TableID");
                    int playerid1 = reader.GetInt32("PlayerID1");
                    int playerid2 = reader.GetInt32("PlayerID2");
                    int payment = 0; int cardid = -1; string comment = "";
                    try
                    {
                        payment = reader.GetInt32("Payment");
                    }
                    catch { }
                    try
                    {
                        cardid = reader.GetInt32("CardID");
                    }
                    catch { }
                    try
                    {
                        comment = reader.GetString("Comment");
                    }
                    catch { }
                    info = new TableInfo(tableid, playerid1, playerid2, payment, cardid, comment);
                    res.Add(info);
                }
                reader.Close();

            }
            else
            {
                Disconnect();
                return null;
            }
            Disconnect();

            //Fill Nick Name
            foreach (var item in res)
            {
                item.NickName1 = GetNickName(item.PlayerID1);
                item.NickName2 = GetNickName(item.PlayerID2);
            }
            return res;
        }
        //public List<TableInfo> GetAllTableInRoom()
        //{
        //    string commandString = "SELECT * FROM `table`";
        //    MySqlCommand command = new MySqlCommand(commandString, connection);
        //    Connect();
        //    MySqlDataReader reader = command.ExecuteReader();
        //    TableInfo info = null;
        //    List<TableInfo> res = new List<TableInfo>();
        //    if (reader.HasRows)
        //    {
        //        while(reader.Read())
        //        {
        //            int tableid = reader.GetInt32("TableID");
        //            int playerid1 = reader.GetInt32("PlayerID1");
        //            int playerid2 = reader.GetInt32("PlayerID2");
        //            int payment=0; int cardid =-1; string comment ="";
        //            try
        //            {
        //                payment = reader.GetInt32("Payment");
        //            }
        //            catch { }
        //            try
        //            {
        //                cardid = reader.GetInt32("CardID");
        //            }
        //            catch { }
        //            try
        //            {
        //                comment = reader.GetString("Comment");
        //            }
        //            catch { }
        //            info = new TableInfo(tableid, playerid1, playerid2, payment, cardid, comment);
        //            res.Add(info);
        //        }
        //        reader.Close();

        //    }
        //    else
        //    {
        //        Disconnect();
        //        return null;
        //    }
        //    Disconnect();

        //    //Fill Nick Name
        //    foreach (var item in res)
        //    {
        //        item.NickName1 = GetNickName(item.PlayerID1);
        //        item.NickName2 = GetNickName(item.PlayerID2);
        //    }
        //    return res;
        //}

        public void RemoveAllTable()
        {
            string commandString = String.Format("TRUNCATE TABLE `table`");
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            int res = command.ExecuteNonQuery();
            Disconnect();
        }

        public void CreatTable(int TableID, int IDUserCreate)
        {
            string commandString = String.Format("INSERT INTO `table` (`TableID`,`PlayerID1`,`PlayerID2`) VALUES ('{0}','{1}','-1')", TableID, IDUserCreate);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            int res = command.ExecuteNonQuery();
            if (res != 1)
                Console.WriteLine("Error When Excute : {0}", commandString);
            Disconnect();
        }
        public void JoinTable(int TableID, int UserIDJoin)
        {
            string commandString = String.Format("UPDATE `table` SET `PlayerID2`='{0}' WHERE (`TableID`='{1}') AND (`PlayerID2`='{2}')", UserIDJoin, TableID, -1);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            int res = command.ExecuteNonQuery();
            if (res != 1)
                Console.WriteLine("error jointable");
            //Console.WriteLine("{0} excute", res);
            Disconnect();

            //if(res == 0)
            //{
            //    commandString = String.Format("UPDATE `table` SET `PlayerID2`='{0}' WHERE (`TableID`='{1}') AND (`PlayerID2`='{2}')  ", UserIDJoin, TableID,-1);
            //    command = new MySqlCommand(commandString, connection);
            //    Connect();
            //    res = command.ExecuteNonQuery();
            //    Console.WriteLine("{0} excute", res);
            //    Disconnect();
            //}
        }
        public void OutTable(int TableID, int UserIDOut)
        {
            string commandString = String.Format("UPDATE `table` SET `PlayerID2`='{0}' WHERE (`TableID`='{1}') AND (`PlayerID2`='{2}')  ", -1, TableID, UserIDOut);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            int res = command.ExecuteNonQuery();
            //Console.WriteLine("{0} excute",res);
            Disconnect();

            if (res == 0)
            {
                commandString = String.Format("UPDATE `table` SET `PlayerID1`='{0}' WHERE (`TableID`='{1}') AND (`PlayerID1`='{2}')  ", -1, TableID, UserIDOut);
                command = new MySqlCommand(commandString, connection);
                Connect();
                res = command.ExecuteNonQuery();
                Console.WriteLine("{0} excute", res);
                Disconnect();
            }

        }
        public void RemoveTable(int TableID)
        {

            string commandString = String.Format("DELETE FROM `table` WHERE (`TableID`='{0}')", TableID);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            int res = command.ExecuteNonQuery();
            if (res != 1)
                Console.WriteLine("error removetable");
            //Console.WriteLine("{0} excute ",res);
            Disconnect();

        }



        #endregion

        #region friend
        public List<PlayerInfo> GetFriendsOfUser(int UserID)
        {
            string commandString = string.Format("SELECT * from `user` where ID in (SELECT UserID2 FROM friends where UserID1 = '{0}') UNION SELECT * from `user` where ID in (SELECT UserID1 FROM friends where UserID2 = '{1}')", UserID, UserID);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            PlayerInfo info = null;
            List<PlayerInfo> res = new List<PlayerInfo>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    info = new PlayerInfo();
                    info.ID = reader.GetInt32("ID");
                    info.Level = reader.GetInt32("Level");
                    info.NickName = reader.GetString("NickName");
                    info.IsOnline = reader.GetInt16("IsOnline");
                    res.Add(info);
                }
                reader.Close();

            }
            else
            {
                Disconnect();
                return null;
            }
            Disconnect();
            return res;
        }
        #endregion

        #region room
        public List<RoomInfo> GetAllRoom()
        {
            string commandString = "SELECT * FROM `room`";
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            List<RoomInfo> res = new List<RoomInfo>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int roomid = reader.GetInt32("RoomID");
                    int serverid = reader.GetInt32("ServerID");
                    string name = reader.GetString("Name");
                    int level = reader.GetInt32("Level");
                    RoomInfo tmp = new RoomInfo(roomid, serverid, name, level);
                    res.Add(tmp);
                }
                reader.Close();
            }
            else
            {
                Disconnect();
                return null;
            }
            Disconnect();
            return res;
        }
        //public List<RoomInfo> GetRoomOfServer(byte ServerID)
        //{
        //    string commandString = string.Format("SELECT * FROM `room` where ServerID = '{0}'", ServerID);
        //    MySqlCommand command = new MySqlCommand(commandString, connection);
        //    Connect();
        //    MySqlDataReader reader = command.ExecuteReader();
        //    List<RoomInfo> res = new List<RoomInfo>();
        //    if (reader.HasRows)
        //    {
        //        while (reader.Read())
        //        {
        //            RoomInfo tmp = new RoomInfo(reader.GetInt32("RoomID"), reader.GetInt32("ServerID"), reader.GetString("Name"), reader.GetInt32("Level"));
        //            res.Add(tmp);
        //        }
        //        reader.Close();
        //    }
        //    else
        //    {
        //        Disconnect();
        //        return null;
        //    }
        //    Disconnect();
        //    return res;
        //}
        public List<int> GetAllUser(int RoomID)
        {
            string commandString = string.Format("SELECT ID FROM `user` where RoomID = '{0}'", RoomID);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            List<int> res = new List<int>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    res.Add(reader.GetInt32("ID"));
                }
                reader.Close();
            }
            else
            {
                Disconnect();
                return null;
            }
            Disconnect();
            return res;
        }
        public List<int> GetAllUserInTable(int RoomID)
        {
            string commandString = string.Format("SELECT ID FROM `user` where RoomID = '{0}' AND (ID in (SELECT PlayerID1 FROM `table`) OR ID in (SELECT PlayerID2 FROM `table`))", RoomID);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            List<int> res = new List<int>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    res.Add(reader.GetInt32("ID"));
                }
                reader.Close();
            }
            else
            {
                Disconnect();
                return null;
            }
            Disconnect();
            return res;
        }
        //public int CountUser(int RoomID)
        //{
        //    String CommandString = string.Format("select count(*) as total from `user` where RoomID = '{0}'", RoomID);
        //    MySqlCommand Command = new MySqlCommand(CommandString, connection);
        //    int count = 0;
        //    MySqlDataReader reader = Command.ExecuteReader();
        //    if (reader.HasRows)
        //    {
        //        while (reader.Read())
        //        {
        //            count = reader.GetInt32("total");
        //        }
        //        reader.Close();
        //    }
        //    else return -1;
        //    return count;
        //}
        //public int CountCreater(int RoomID)
        //{
        //    String CommandString = string.Format("select count(*) as total from `user` inner join `table` on ID = PlayerID1 where RoomID = '{0}'", RoomID);
        //    MySqlCommand Command = new MySqlCommand(CommandString, connection);
        //    int count = 0;
        //    MySqlDataReader reader = Command.ExecuteReader();
        //    if (reader.HasRows)
        //    {
        //        while (reader.Read())
        //        {
        //            count = reader.GetInt32("total");
        //        }
        //        reader.Close();
        //    }
        //    else return -1;
        //    return count;
        //}
        public void RemoveRoom(int RoomID)
        {
            string commandString = String.Format("DELETE FROM `room` WHERE (`RoomID`='{0}')", RoomID);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            int res = command.ExecuteNonQuery();
            //Console.WriteLine("{0} excute: RemoveRoom", res);
            Disconnect();
        }
        public void CreateRoom(RoomInfo roominfo)
        {
            try
            {
                string commandString = String.Format("INSERT INTO `room`(`RoomID`, `ServerID`, `Name`, `Level`) VALUE('{0}', '{1}', '{2}', '{3}')", roominfo.RoomID, roominfo.ServerId, roominfo.Name, roominfo.Level);
                MySqlCommand command = new MySqlCommand(commandString, connection);
                Connect();
                int res = command.ExecuteNonQuery();
                if (res == 0)
                    Console.WriteLine("Error When Excute : {0}", commandString);
                Disconnect();
            }
            catch
            {
                Disconnect();
            }
        }
        public void JoinRoom(int RoomID, int UserIDJoin)
        {
            string commandString = String.Format("UPDATE user SET RoomID = '{0}' WHERE ID = '{1}'", RoomID, UserIDJoin);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            int res = command.ExecuteNonQuery();
            if (res != 1)
                Console.WriteLine("error joinroom");
            //Console.WriteLine("{0} excute: JoinRoom", res);
            Disconnect();
        }
        public void OutRoom(int RoomID, int UserIDOut)
        {
            string commandString = String.Format("UPDATE `user` SET `RoomID` = DEFAULT WHERE( `ID` = '{0}')", UserIDOut);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            int res = command.ExecuteNonQuery();
            if (res != 1)
                Console.WriteLine("error outroom");
            //Console.WriteLine("{0} excute: OutRoom", res);
            Disconnect();
        }
        internal void RemoveAllRoom()
        {
            string commandString2 = String.Format("TRUNCATE TABLE `room`");
            MySqlCommand command2 = new MySqlCommand(commandString2, connection);
            Connect();
            int res = command2.ExecuteNonQuery();
            Disconnect();
        }
        public int GetRoom(int UserCreateID)
        {
            string commandString = string.Format("select RoomID from `user` where ID = '{0}'", UserCreateID);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            int RoomID = 0;
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    try
                    {
                        RoomID = reader.GetInt32("RoomID");
                    }
                    catch
                    {
                        reader.Close();
                        Disconnect();
                        return -1;
                    }
                }
                reader.Close();
            }
            else
            {
                Disconnect();
                return -1;
            }
            Disconnect();
            return RoomID;
        }
        #endregion
        #region Deck & Storage
        public PlayerInfo GetDeckStore(int id_player)
        {

            string commandString = "select * from cardowner where IDPlayer='" + id_player.ToString() + "'";
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            PlayerInfo info = new PlayerInfo();
            info.ID = id_player;
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int soluongsohuu = (int)reader["SoLuongSoHuu"];
                    int soluongtrongbobai = (int)reader["SoLuongTrongBoBai"];
                    int type_id = (int)reader["IDCard"];
                    for (int i = 0; i < soluongsohuu; i++)
                    {
                        info.Storage.Add(type_id);
                    }
                    for (int i = 0; i < soluongtrongbobai; i++)
                    {
                        info.Deck.Add(type_id);
                    }
                }
                reader.Close();
            }
            else
            {
                Disconnect();
                return null;
            }
            Disconnect();
            return info;
        }
        #endregion

        #region Forbidden
        public List<ForbiddenInfo> GetAllForbidden()
        {
            string commandString = "SELECT * FROM `forbidden_card`";
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            List<ForbiddenInfo> result = new List<ForbiddenInfo>();
            MySqlDataReader reader = command.ExecuteReader();
            ForbiddenInfo info = null;
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    info = new ForbiddenInfo();
                    info.ID = reader.GetInt32("ID");
                    info.MaxNumber = reader.GetInt32("Max_Number_InDeck");
                    result.Add(info);
                }
                reader.Close();
            }
            else
            {
                Disconnect();
                return null;
            }
            Disconnect();
            return result;
        }
        #endregion

        //------------------------------Util---------------------------
        #region Util
        private byte[] GetByte(String value)
        {
            return UTF8Encoding.UTF8.GetBytes(value);
        }
        private string GetString(byte[] value)
        {
            StringBuilder res = new StringBuilder();
            foreach (byte item in value)
            {
                res.Append(item.ToString("X2"));
            }
            return res.ToString();

        }
        #endregion
    }
}
